import uuid

import pymysql
from pymysql import Error
from pymysql.cursors import DictCursor


def sync_user_to_ymt():
    # 数据库连接配置
    target_db_config = {
        'host': '10.1.213.159',
        'user': 'GTXS_user',
        'password': 'GTXS_user_JG123',
        'database': 'sysmandb',
        'charset': 'utf8mb4'
    }

    source_db_config = {
        'host': '10.99.81.155',
        'port': 8306,
        'user': 'root',
        'password': 'Lgmi6395',
        'database': 'jg_usercenter',
        'charset': 'utf8mb4'
    }

    try:
        # 连接源数据库和目标数据库
        source_conn = pymysql.connect(**source_db_config, cursorclass=DictCursor)
        target_conn = pymysql.connect(**target_db_config, cursorclass=DictCursor)
        with source_conn.cursor() as source_cursor, target_conn.cursor() as target_cursor:
            source_cursor.execute("""
                select id,
                       user_name,
                       real_name,
                       password,
                       salt,
                       avatar,
                       birthday,
                       sex,
                       email,
                       phone,
                       org_code,
                       state,
                       create_user,
                       create_date,
                       change_user,
                       change_date,
                       depart_ids,
                       corp_code,
                       user_code,
                       user_type,
                       id_card,
                       tenant_id,
                       remark,
                       deleted_tag
                from sys_user u where 1=1
                                  and not exists (select 1 from ymt_usercenter.sys_user yu where yu.id = u.id)
                                  and u.remark not like 'sync%'
                                -- and u.create_date >= '2025-08-01'
            """)
            source_data = source_cursor.fetchall()

            count = 0
            for row in source_data:
                guid = str(uuid.uuid4())
                row['guid'] = guid.replace("-", "")
                insert_sql = """
                    INSERT INTO sys_user (guid, tenant_id, id, user_name, user_nickname, user_email, user_password, 
                        department, user_tel, user_type, jg_num, credit_id, user_birthday, user_wechat, user_isvalid, 
                        user_create_time, user_create_user, user_branch_id, user_branch_name, user_avatar, deleted_tag, 
                        create_date, create_user, change_date, change_user, user_code) 
                    VALUES (%(guid)s, '010', %(id)s, %(user_name)s, %(real_name)s, %(email)s, %(password)s, NULL, %(phone)s, 
                        'DRIVER', null, %(id_card)s, %(birthday)s, %(phone)s, 1, NOW(), '1', 2, NULL, %(avatar)s, 
                        'N', %(create_date)s, 'sysUser', %(change_date)s, 'sysUser', %(user_code)s)
                """

                # 调试输出SQL
                debug_sql(target_cursor, insert_sql, row)

                target_cursor.execute(insert_sql, row)

                count += 1
                print(count)

            # 提交事务
            target_conn.commit()

            print(f"同步用户数据完成！总计: {count} 条记录")

    except Error as e:
        print(f"数据库操作出错: {e}")
        # 回滚事务
        if 'target_conn' in locals():
            target_conn.rollback()
    finally:
        # 关闭数据库连接
        if 'source_conn' in locals():
            source_conn.close()
        if 'target_conn' in locals():
            target_conn.close()


def debug_sql(cursor, sql, params=None):
    # 获取将要执行的SQL
    full_sql = cursor.mogrify(sql, params or ())
    if isinstance(full_sql, bytes):
        full_sql = full_sql.decode('utf-8')
    print("DEBUG SQL:", full_sql)
    return full_sql


if __name__ == '__main__':
    sync_user_to_ymt()
